import xlrd
import sqlite3
from sqlalchemy import create_engine
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy.sql import select
from sqlalchemy.sql import and_
import os

if (os.path.exists('bdd-sqlalchemy.db')):
	os.unlink('bdd-sqlalchemy.db')

engine = create_engine('sqlite:///bdd-sqlalchemy.db', echo=True)

metadata = MetaData(bind=engine)

#listRowBdd = ['estabelecimento','unidade','nivel','curso','area','9596h']
listbdd = []
intCounter = 0
primKey = 0
strQuery = []

ESTAB_ENSINO = ''
UNI_ORGANICA = ''
NIVEL_FORMACAO = ''
CURSO_SUP = ''
AREA_EDU_FOR = ''

wb = xlrd.open_workbook("hippie-mini-mini.xls")

listRowBdd = [
'estabelecimento','unidade','nivel','curso','area',
'9596h','9596m','9596hm','9697h','9697m','9697hm',
'9798h','9798m','9798hm','9899h','9899m','9899hm',
'9900h','9900m','9900hm','0001h','0001m','0001hm',
'0102h','0102m','0102hm','0203h','0203m','0203hm',
'0304h','0304m','0304hm','0405h','0405m','0405hm',
'0506h','0506m','0506hm','0607h','0607m','0607hm',
'0708h','0708m','0708hm','0809h','0809m','0809hm',
'0910h','0910m','0910hm','1011h','1011m','1011hm'
]

users_table = Table('elementsbdd', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('estabelecimento', String),
                    Column('unidade', String),
                    Column('nivel', String),
                    Column('curso', String),
                    Column('area', String),
                    Column('9596h', String),
                    Column('9596m', String),
                    Column('9596hm', String),
                    Column('9697h', String),
                    Column('9697m', String),
                    Column('9697hm', String),
                    Column('9798h', String),
                    Column('9798m', String),
                    Column('9798hm', String),
                    Column('9899h', String),
                    Column('9899m', String),
                    Column('9899hm', String),
                    Column('9900h', String),
                    Column('9900m', String),
                    Column('9900hm', String),
                    Column('0001h', String),
                    Column('0001m', String),
                    Column('0001hm', String),
                    Column('0102h', String),
                    Column('0102m', String),
                    Column('0102hm', String),
                    Column('0203h', String),
                    Column('0203m', String),
                    Column('0203hm', String),
                    Column('0304h', String),
                    Column('0304m', String),
                    Column('0304hm', String),
                    Column('0405h', String),
                    Column('0405m', String),
                    Column('0405hm', String),
                    Column('0506h', String),
                    Column('0506m', String),
                    Column('0506hm', String),
                    Column('0607h', String),
                    Column('0607m', String),
                    Column('0607hm', String),
                    Column('0708h', String),
                    Column('0708m', String),
                    Column('0708hm', String),
                    Column('0809h', String),
                    Column('0809m', String),
                    Column('0809hm', String),
                    Column('0910h', String),
                    Column('0910m', String),
                    Column('0910hm', String),
                    Column('1011h', String),
                    Column('1011m', String),
                    Column('1011hm', String)
                )
metadata.create_all()

'''
tableBdd = Table('elementsbdd', metadata,
                    Column('id', Integer),
                    Column('estabelecimento', String),
                    Column('unidade', String),
                    Column('nivel', String),
					Column('curso', String),
					Column('area', String),
					Column('9596h', String)
				)
metadata.create_all()
'''

# SHEET
for sheetname in wb.sheet_names() :
    sh = wb.sheet_by_name("Sheet1")
    
    checkCol = 0
#    counterCol = 0

    #for rownum in range(sh.nrows):
    for col in range(sh.ncols):
        if (sh.cell(3,col).value == ""):
            checkCol = col
                #print "COL = %i\n" %col
                #print "COL = %s\n" %i
    #print "COL = %i\n" % checkCol
    #print "TXT = %s\n" % sh.cell(3,checkCol)
    #raw_input("Press Enter to continue...")
    # LINHA
    for rownum in range(4, sh.nrows, 1):
        
        listbdd[:] = []
        intCounter = 0
        #a[:] = []

        if sh.cell_value(rownum,0) == 'TOTAL':
            break
        
        else:
            if sh.cell_value(rownum,0) != '':
                ESTAB_ENSINO = sh.cell_value(rownum, 0).encode('latin-1')

            if sh.cell_value(rownum, 1) != '':
                UNI_ORGANICA = sh.cell_value(rownum, 1).encode('latin-1')

            if sh.cell_value(rownum,2) != '':
                NIVEL_FORMACAO = sh.cell_value(rownum, 2).encode('latin-1')

            if sh.cell_value(rownum,3) != '':
                CURSO_SUP = sh.cell_value(rownum, 3).encode('latin-1')

            if sh.cell_value(rownum,4) != '':
                AREA_EDU_FOR = sh.cell_value(rownum, 4).encode('latin-1')

            # COLUNA

            strQuery = ""

            for col in range(sh.ncols):         
            
#                for rownum in range(2, 2, 1):

                if (col != checkCol):
                    #continue
#                else: 
                    if sh.cell(rownum,col).value == "-":
                        listbdd.append(str("0.0"))
                    elif isinstance(sh.cell(rownum,col).value, float):
                        listbdd.append(str(sh.cell(rownum,col).value))
                    else:
                        if (sh.cell(rownum,col).value) == '' and col == 0:
                            listbdd.append(str(ESTAB_ENSINO))
                        elif (sh.cell(rownum,col).value) == '' and col == 1:
                            listbdd.append(str(UNI_ORGANICA))
                        elif (sh.cell(rownum,col).value) == '' and col == 2:
                            listbdd.append(str(NIVEL_FORMACAO))
                        elif (sh.cell(rownum,col).value) == '' and col == 3:
                            listbdd.append(str(CURSO_SUP))
                        elif (sh.cell(rownum,col).value) == '' and col == 4:
                            listbdd.append(str(AREA_EDU_FOR))
                        else:
                            listbdd.append(str((sh.cell(rownum,col).value).encode('latin-1')))  

                #print "\nTOPO = %s" % sh.cell(2,col).value

            intCounter = 0

            #print listbdd

            #totalListBdd = len(listbdd)

            strQuery = "INSERT INTO elementsbdd VALUES (%i" % primKey
#, 'X', 'A', 'B', 'C', 'D', 'E')
            con = engine.connect()

            for x in listbdd:
                #print "# Col -> %i " % intCounter
                #if intCounter == 2:
                #    break
                strQuery += ", \'%s\'" %x
                #a = "INSERT INTO elementsbdd VALUES (%i, 'X', 'A', 'B', 'C', 'D', 'E')" % primKey
                #a = "INSERT INTO elementsbdd VALUES (%i, %s, %s, %s, %s, %s, %s)" % primKey %listbdd[0] %listbdd[1] %listbdd[2] %listbdd[3] %listbdd[4] %listbdd[5]
                #a = "INSERT INTO elementsbdd VALUES (%i, \
                #    %s, %s, %s, %s, %s, \
                #    %i)" % primKey %listbdd[0] %listbdd[1] %listbdd[2] \
                #    %listbdd[3] %listbdd[4] %listbdd[5] %listbdd[6]
                #print x
            strQuery += ")"    
            print "\n- QUERY -> %s" % strQuery 

            #con.execute(a)
            # conn.execute("INSERT INTO users VALUES (1, 'john')")
            primKey += 1
            intCounter += 1

            print "\n# Lin -> %i \n" % rownum 
            #raw_input("Press Enter to continue...")
    
    
            # FIM COLUNA
    # FIM LINHA
# FIM SHEET
print "### END ###"
# select * from elementsbdd
